package daoImpl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import dao.ForumDao;
import pojo.Answer;
import pojo.AnswerComment;
import pojo.Article;
import pojo.ArticleComment;
import pojo.Question;
import pojo.Resource;
import pojo.ResourceComment;
import utils.DateUtils;
import utils.DruidUtils;

public class ForumDaoImpl implements ForumDao{
	
	
	
	@Override
	public boolean updateAnswerNumByqid3(int aid, int aCommentNum) {
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		try {
			String sql = "update answer set aCommentNum=? where aid=?";
			prep = conn.prepareStatement(sql);
			prep.setInt(1, aCommentNum);
			prep.setInt(2, aid);
			int result = prep.executeUpdate();
			if (result > 0) {
				return true;
			}
			return false;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if( prep !=null)   prep.close();
				if (conn != null)  conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public boolean updateAnswerNumByqid2(int aid, int aReportNum) {
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		try {
			String sql = "update answer set aReportNum=? where aid=?";
			prep = conn.prepareStatement(sql);
			prep.setInt(1, aReportNum);
			prep.setInt(2, aid);
			int result = prep.executeUpdate();
			if (result > 0) {
				return true;
			}
			return false;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if( prep !=null)   prep.close();
				if (conn != null)  conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public boolean updateAnswerNumByqid1(int aid, int aForkNum) {
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		try {
			String sql = "update answer set aForkNum=? where aid=?";
			prep = conn.prepareStatement(sql);
			prep.setInt(1, aForkNum);
			prep.setInt(2, aid);
			int result = prep.executeUpdate();
			if (result > 0) {
				return true;
			}
			return false;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if( prep !=null)   prep.close();
				if (conn != null)  conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public Answer getAnswerNumByqid(int aid) {
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		ResultSet res=null;
		Answer a = null;
		try {
			String sql = "select *from answer where aid =?";
		    prep = conn.prepareStatement(sql);
			prep.setInt(1, aid);
            res = prep.executeQuery();
            if(res.next()) {
				int aid01 =res.getInt("aid");
				int qid =res.getInt("qid");
				int userid =res.getInt("userid");
				String aContent=res.getString("aContent");

				int aLoveNum =res.getInt("aLoveNum");
				int aForkNum =res.getInt("aForkNum");
				int aReportNum =res.getInt("aReportNum");
				int aCommentNum =res.getInt("aCommentNum");
				Date aTime =res.getDate("aTime");
				a=new Answer(aid01,qid,userid,aContent,aLoveNum,aForkNum,aReportNum,aCommentNum,aTime);

            }
            return a;
            
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if( res !=null)   res.close();
				if( prep !=null)   prep.close();
				if (conn != null)  conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public boolean updateAnswerNumByqid(int aid, int aLoveNum) {
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		try {
			String sql = "update answer set aLoveNum=? where aid=?";
			prep = conn.prepareStatement(sql);
			prep.setInt(1, aLoveNum);
			prep.setInt(2, aid);
			int result = prep.executeUpdate();
			if (result > 0) {
				return true;
			}
			return false;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if( prep !=null)   prep.close();
				if (conn != null)  conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public Question getFocusNumByqid(int qid) {
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		ResultSet res=null;
		Question q = null;
		try {
			String sql = "select *from question where qid =?";
		    prep = conn.prepareStatement(sql);
			prep.setInt(1, qid);
            res = prep.executeQuery();
            if(res.next()) {
				int qid01 =res.getInt("qid");
				String qTitle = res.getString("qTitle");
				String qContent=res.getString("qContent");
				int userid =res.getInt("userid");
//				String userName =res.getString("userName");
//				String userIma =res.getString("userIma");
				Date qTime =res.getDate("qTime");
				int qFocusNum =res.getInt("qFocusNum");
				int qAnswerNum =res.getInt("qAnswerNum");
				q=new Question(qid,qTitle,qContent,userid,qTime,qFocusNum,qAnswerNum);

            }
            return q;
            
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if( res !=null)   res.close();
				if( prep !=null)   prep.close();
				if (conn != null)  conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public boolean updateFocusNumByqid(int qid, int qFocusNum02) {
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		try {
			String sql = "update question set qFocusNum=? where qid=?";
			prep = conn.prepareStatement(sql);
			prep.setInt(1, qFocusNum02);
			prep.setInt(2, qid);
			int result = prep.executeUpdate();
			if (result > 0) {
				return true;
			}
			return false;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if( prep !=null)   prep.close();
				if (conn != null)  conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public ArrayList<Resource> queryResourceByTitle(String resource) {
		ArrayList<Resource> resources = new ArrayList<Resource>();
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		ResultSet res=null;
		Resource r = null;
		try {
			String sql = "select resource.*,user.userIma,user.userName,user.motto from resource,user where resource.userid=user.userid and rsTitle like ?";
			prep = conn.prepareStatement(sql);
			prep.setString(1, "%"+resource+"%");
		    res = prep.executeQuery();
			while(res.next()) {
				int rsid =res.getInt("rsid");
				int uaid =res.getInt("userid");
				String userName =res.getString("userName");
				String userIma =res.getString("userIma");
				String motto = res.getString("motto");
				String Ima = res.getString("rsIma");
				String rsTitle = res.getString("rsTitle");
				String rsContent=res.getString("rsContent");
				int rsLoveNum =res.getInt("rsLoveNum");
				int rsForkNum =res.getInt("rsForkNum");
				int rsReportNum =res.getInt("rsReportNum");
				int rsCommentNum =res.getInt("rsCommentNum");
				Date rsTime =res.getDate("rsTime");
				r=new Resource(rsid,uaid,userName,userIma,motto,Ima,rsTitle,rsContent,rsLoveNum,rsForkNum,rsReportNum,rsCommentNum,rsTime);
				resources.add(r);

			}

			res.close();
			prep.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resources;
	}

	@Override
	public ArrayList<Article> queryArticleByTitle(String article) {
			ArrayList<Article> articles = new ArrayList<Article>();
			Connection conn = DruidUtils.getConnection();
			PreparedStatement prep =null;
			ResultSet res=null;
			Article a= null;
			try {
				String sql = "select article.*,user.userIma,user.userName,user.motto from article,user where article.userid=user.userid and artTitle like ?";
				prep = conn.prepareStatement(sql);
				prep.setString(1, "%"+article+"%");
			    res = prep.executeQuery();
				while(res.next()) {
					int aid =res.getInt("artid");
					int uaid =res.getInt("userid");
					String userName =res.getString("userName");
					String userIma =res.getString("userIma");
					String motto = res.getString("motto");
					String aTitle = res.getString("artTitle");
					String aContent=res.getString("artContent");
					int artLoveNum =res.getInt("artLoveNum");
					int artForkNum =res.getInt("artForkNum");
					int artReportNum =res.getInt("artReportNum");
					int artCommentNum =res.getInt("artCommentNum");
					Date aTime =res.getDate("artTime");
					a=new Article(aid,uaid,userName,userIma,motto,aTitle,aContent,artLoveNum,artForkNum,artReportNum,artCommentNum,aTime);
					articles.add(a);

				}

				res.close();
				prep.close();
				conn.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return articles;
		
	}

	//根据标题搜问题
	@Override
	public ArrayList<Question> queryQuestionByTitle(String question){
		ArrayList<Question> questions = new ArrayList<Question>();
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		ResultSet res=null;
		Question q = null;
		try {
			String sql = "select question.*,user.userIma,user.userName from question,user where question.userid=user.userid and qTitle like ?";
			prep = conn.prepareStatement(sql);
			prep.setString(1, "%"+question+"%");
		    res = prep.executeQuery();
			while(res.next()) {
				int qid =res.getInt("qid");
				String qTitle = res.getString("qTitle");
				String qContent=res.getString("qContent");
				int userid =res.getInt("userid");
				String userName =res.getString("userName");
				String userIma =res.getString("userIma");
				Date qTime =res.getDate("qTime");
				int qFocusNum =res.getInt("qFocusNum");
				int qAnswerNum =res.getInt("qAnswerNum");
				q=new Question(qid,qTitle,qContent,userid,userName,userIma,qTime,qFocusNum,qAnswerNum);
				questions.add(q);
				
			}

			res.close();
			prep.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return questions;
	}

	@Override
	public ArrayList<Question> getAllQuestion() {
		Connection conn = DruidUtils.getConnection();
		String sql = "select question.*,user.userIma,user.userName from question,user where question.userid=user.userid;";
		ArrayList<Question> qList = new ArrayList<Question>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				Question q = new Question();
				q.setQid(res.getInt("qid"));
				q.setqTitle(res.getString("qTitle"));
				q.setqContent(res.getString("qContent"));
				q.setUserid(res.getInt("userid"));
				q.setUserName(res.getString("userName"));
				q.setUserIma(res.getString("userIma"));
				q.setqTime(res.getDate("qTime"));
				q.setqFocusNum(res.getInt("qFocusNum"));
				q.setqAnswerNum(res.getInt("qAnswerNum"));
				qList.add(q);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return qList;
	}

	@Override
	public ArrayList<Answer> getAnswerByqid(int qid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from (select answer.*,user.userIma,user.userName,user.motto from answer,user where answer.userid=user.userid) as temp where qid="+qid;
		ArrayList<Answer> aList = new ArrayList<Answer>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				Answer a = new Answer();
				a.setAid(res.getInt("aid"));
				a.setQid(res.getInt("qid"));
				a.setUserid(res.getInt("userid"));
				a.setUserName(res.getString("userName"));
				a.setUserIma(res.getString("userIma"));
				a.setMotto(res.getString("motto"));
				a.setaContent(res.getString("aContent"));
				a.setaLoveNum(res.getInt("aLoveNum"));
				a.setaForkNum(res.getInt("aForkNum"));
				a.setaReportNum(res.getInt("aReportNum"));
				a.setaCommentNum(res.getInt("aCommentNum"));
				a.setaTime(res.getDate("aTime"));
				aList.add(a);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return aList;
	}

	@Override
	public ArrayList<AnswerComment> getAnswerComment(int aid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from (select answercomment.*,user.userIma,user.userName from answercomment,user where answercomment.userid=user.userid) as temp where aid=?";
		ArrayList<AnswerComment> acList = new ArrayList<AnswerComment>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			pre.setInt(1, aid);
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				AnswerComment ac = new AnswerComment();
				ac.setAcid(res.getInt("acid"));
				ac.setAid(res.getInt("aid"));
				ac.setUserid(res.getInt("userid"));
				ac.setUserIma(res.getString("userIma"));
				ac.setUserName(res.getString("userName"));
				ac.setAcContent(res.getString("acContent"));
				ac.setAcTime(res.getDate("acTime"));
				ac.setAcLoveNum(res.getInt("acLoveNum"));
				acList.add(ac);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return acList;
	}

	@Override
	public ArrayList<Article> getAllArticle() {
		Connection conn = DruidUtils.getConnection();
		String sql = "select article.*,user.userIma,user.userName,user.motto from article,user where article.userid=user.userid;";
		ArrayList<Article> artList = new ArrayList<Article>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				Article art = new Article();
				art.setArtid(res.getInt("artid"));
				art.setUserid(res.getInt("userid"));
				art.setUserName(res.getString("userName"));
				art.setUserIma(res.getString("userIma"));
				art.setMotto(res.getString("motto"));
				art.setArtTitle(res.getString("artTitle"));
				art.setArtContent(res.getString("artContent"));
				art.setArtLoveNum(res.getInt("artLoveNum"));
				art.setArtForkNum(res.getInt("artForkNum"));
				art.setArtReportNum(res.getInt("artReportNum"));
				art.setArtCommentNum(res.getInt("artCommentNum"));
				art.setArtTime(res.getDate("artTime"));
				artList.add(art);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return artList;
	}

	@Override
	public ArrayList<ArticleComment> getArticleComment(int artid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from (select articlecomment.*,user.userIma,user.userName from articlecomment,user where articlecomment.userid=user.userid) as temp where artid=?";
		ArrayList<ArticleComment> artcList = new ArrayList<ArticleComment>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			pre.setInt(1, artid);
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				ArticleComment artc = new ArticleComment();
				artc.setArtcid(res.getInt("artcid"));
				artc.setArtid(res.getInt("artid"));
				artc.setUserid(res.getInt("userid"));
				artc.setUserIma(res.getString("userIma"));
				artc.setUserName(res.getString("userName"));
				artc.setArtcContent(res.getString("artcContent"));
				artc.setArtcTime(res.getDate("artcTime"));
				artc.setartcLoveNum(res.getInt("artcLoveNum"));
				artcList.add(artc);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return artcList;
	}

	@Override
	public ArrayList<Resource> getAllResource() {
		Connection conn = DruidUtils.getConnection();
		String sql = "select resource.*,user.userIma,user.userName,user.motto from resource,user where resource.userid=user.userid;";
		ArrayList<Resource> rsList = new ArrayList<Resource>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				Resource rs = new Resource();
				rs.setRsid(res.getInt("rsid"));
				rs.setUserid(res.getInt("userid"));
				rs.setUserName(res.getString("userName"));
				rs.setUserIma(res.getString("userIma"));
				rs.setMotto(res.getString("motto"));
				rs.setRsIma(res.getString("rsIma"));
				rs.setRsTitle(res.getString("rsTitle"));
				rs.setRsContent(res.getString("rsContent"));
				rs.setRsLoveNum(res.getInt("rsLoveNum"));
				rs.setRsForkNum(res.getInt("rsForkNum"));
				rs.setRsReportNum(res.getInt("rsReportNum"));
				rs.setRsCommentNum(res.getInt("rsCommentNum"));
				rs.setRsTime(res.getDate("rsTime"));
				rsList.add(rs);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rsList;
	}

	@Override
	public ArrayList<ResourceComment> getResourceComment(int rsid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from (select resourcecomment.*,user.userIma,user.userName from resourcecomment,user where resourcecomment.userid=user.userid) as temp where rsid=?";
		ArrayList<ResourceComment> rscList = new ArrayList<ResourceComment>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			pre.setInt(1, rsid);
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				ResourceComment rsc = new ResourceComment();
				rsc.setRscid(res.getInt("rscid"));
				rsc.setRsid(res.getInt("rsid"));
				rsc.setUserid(res.getInt("userid"));
				rsc.setUserIma(res.getString("userIma"));
				rsc.setUserName(res.getString("userName"));
				rsc.setRscContent(res.getString("rscContent"));
				rsc.setRscTime(res.getDate("rscTime"));
				rsc.setRscLoveNum(res.getInt("rscLoveNum"));
				rscList.add(rsc);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rscList;
	}

	@Override
	public int sendAcComment(int aid, int userid, String acComment) {
		Connection conn = DruidUtils.getConnection();
		String sql = "insert into answercomment values(0,?,?,?,?,0)";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setInt(1, aid);
			pre.setInt(2, userid);
			pre.setString(3, acComment);
			pre.setString(4, DateUtils.formDate(new Date(), "yyyy-MM-dd hh:mm:ss"));
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int sendArtcComment(int artid, int userid, String artcComment) {
		Connection conn = DruidUtils.getConnection();
		String sql = "insert into articlecomment values(0,?,?,?,?,0)";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setInt(1, artid);
			pre.setInt(2, userid);
			pre.setString(3, artcComment);
			pre.setString(4, DateUtils.formDate(new Date(), "yyyy-MM-dd hh:mm:ss"));
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int sendRscComment(int rsid, int userid, String rscComment) {
		Connection conn = DruidUtils.getConnection();
		String sql = "insert into resourcecomment values(0,?,?,?,?,0)";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setInt(1, rsid);
			pre.setInt(2, userid);
			pre.setString(3, rscComment);
			pre.setString(4, DateUtils.formDate(new Date(), "yyyy-MM-dd hh:mm:ss"));
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int subQuestion(String title, int userid, String content) {
		Connection conn = DruidUtils.getConnection();
		String sql = "insert into question values(0,?,?,?,?,0,0)";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setString(1, title);
			pre.setString(2, content);
			pre.setInt(3, userid);
			pre.setString(4, DateUtils.formDate(new Date(), "yyyy-MM-dd hh:mm:ss"));
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int subExperience(String title, int userid, String content) {
		Connection conn = DruidUtils.getConnection();
		String sql = "insert into article values(0,?,?,?,0,0,0,0,?)";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setInt(1, userid);
			pre.setString(2, title);
			pre.setString(3, content);
			pre.setString(4, DateUtils.formDate(new Date(), "yyyy-MM-dd hh:mm:ss"));
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int subResource(String title, int userid, String content) {
		Connection conn = DruidUtils.getConnection();
		String sql = "insert into resource values(0,?,?,?,0,0,0,?,0,0)";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setString(1, title);
			pre.setInt(2, userid);
			pre.setString(3, content);
			pre.setString(4, DateUtils.formDate(new Date(), "yyyy-MM-dd hh:mm:ss"));
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

}
